package com.study;

import java.io.BufferedReader;
import java.io.Reader;
import java.sql.*;
import java.sql.Date;
import java.util.*;
import java.util.stream.Collectors;

/*/
实现一个商品信息管理的原型系统，具有增删查改等基本功能,必须以Java语言实现。可以是字符界面或者图形
界面。
实验的完整步骤应该是首先创建数据库(可以参考教材398, 399, 407页的代码)，然后编写程序。
任务描述:
首先创建一个名为webstore的数据库
create database webstore;
然后创建一张名为products的表
这张表的结构如下:

CREATE TABLE products (
  id int(11) NOT NULL COMMENT '主键',
  pname varchar(20) COLLATE utf8mb4_bin COMMENT '产名名称',
  brand varchar(20) COLLATE utf8mb4_bin COMMENT  '品牌',
  price float(7,2) COMMENT '价格',
  stock smallint COMMENT '库存',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='产品表';

有了数据库和数据作为基础，可以开始如下实验:
功能需求描述:系统启动后显示如下字符界面的系统菜单(菜单宽度没有严格限制):
*******请选择菜单********
* 1.输出所有商品*
* 2.增加一-种商品*
* 3.删除一-种商品*
* 4.查询-种商品*
* 5.退出系统*
*****************************
请输入你的选择:
如果用户输入1，则在控制台逐行打印所有商品的详细信息(所有字段都打印)
然后系统自动回到主菜单。
如果用户输入2,则增加一种商品，为了简化操作，可以如下处理
首先删除编号为109的商品,(如果products表里没有编号为109的商品也可以执行删除语句)
然后生成一个109号的商品，
商品编号:109
商品名: 3G手机
品牌: Samsung
价格: 3500
库存:10
添加该商品到数据库的products表
在控制台打印:添加成功
然后系统自动回到主菜单
如果用户输入3:
则打印请输入被删除商品的编号:
然后读取用户的输入
如果删除成功，在控制台打印:删除成功
然后系统自动回到主菜单
如果用户输入4:
则打印请输入查询商品的编号:
然后读取用户的输入
如果查询成功，在控制台打印:查询结果是，后面打印该商品的详细信息
如果查询失败，打印:无法找到此商品!
然后系统自动回到主菜单
如果用户输入5:执行System.exit(0)退出系统。
 */
public class Product {

    private final String _ConnectionString="jdbc:mysql://localhost:3306/webstore?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=CTT";
    private final String _Jdbc_Driver="com.mysql.cj.jdbc.Driver";
    private final String _User="root";
    private final String _Password="abc123456";
    private final String _TableName="products";

    private Connection _Connection = null;
    private Statement _Statement = null;
    private PreparedStatement _PrearedStatement = null;
    private ResultSet _ResultSet = null;


    public Product() {
        RegJBDC();
        InitData();
    }

    void RegJBDC() {
        try {
            //System.out.println("注册JBDC驱动");
            Class.forName(_Jdbc_Driver);
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        }
    }

    private void CreateConnection(){
        try{
            //System.out.println("连接数据库");
            _Connection= DriverManager.getConnection(_ConnectionString,_User,_Password);
        }
        catch (SQLException ex){
            ex.printStackTrace();
        }
    }

    private void SetPreparedStatementParameters(PreparedStatement preparedStatement, List<Object> parameters) throws SQLException {
        if (parameters != null && !parameters.isEmpty()) {

            Object value = null;
            for (int i = 0; i < parameters.size(); i++) {
                value = parameters.get(i);
                this.SetPreparedParameter(preparedStatement, i + 1, value);
            }
        }
    }

    private void SetPreparedParameter(PreparedStatement preparedStatement, int index, Object value) throws SQLException {
        if (value == null) {
            preparedStatement.setNull(index, Types.NULL);
        } else if (value instanceof String) {
            preparedStatement.setString(index, value.toString());
        } else if (value instanceof Integer) {
            preparedStatement.setInt(index, (int) value);
        } else if (value instanceof Long) {
            preparedStatement.setLong(index, (long) value);
        } else if (value instanceof Double) {
            preparedStatement.setDouble(index, (double) value);
        } else if (value instanceof Float) {
            preparedStatement.setFloat(index, (float) value);
        } else if (value instanceof Boolean) {
            preparedStatement.setBoolean(index, (boolean) value);
        } else if (value instanceof Byte) {
            preparedStatement.setByte(index, (byte) value);
        } else if (value instanceof Timestamp) {
            preparedStatement.setTimestamp(index, (Timestamp) value);
        } else if (value instanceof java.sql.Date) {
            preparedStatement.setTimestamp(index, new Timestamp(((java.sql.Date) value).getTime()));
        } else if (value instanceof Date) {
            preparedStatement.setTimestamp(index, new Timestamp(((Date) value).getTime()));
        } else {
            preparedStatement.setString(index, value.toString());
        }
    }

    int ExceNoQuery(String sql, List<Object> parameters) {
        try {
            this.CreateConnection();

            if (parameters == null) {
                this._Statement = this._Connection.createStatement();
                return this._Statement.executeUpdate(sql);
            } else {
                this._PrearedStatement = this._Connection.prepareStatement(sql);
                SetPreparedStatementParameters(this._PrearedStatement, parameters);
                return this._PrearedStatement.executeUpdate();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            return 0;
        } finally {
            this.CloseConnction();
        }
    }

    private void CloseConnction() {
        try {
            if (this._Statement != null) {
                this._Statement.close();
                this._Statement = null;
            }

            if (this._PrearedStatement != null) {
                this._PrearedStatement.close();
                this._PrearedStatement = null;
            }

            if (this._ResultSet != null) {
                this._ResultSet.close();
                this._ResultSet = null;
            }

            if (this._Connection != null) {
                this._Connection.close();
                this._Connection = null;
            }
        }
        catch (SQLException ex){
            ex.printStackTrace();
        }
    }

    int Insert(String tableName, Map<String, Object> map) {
        List<String> fieldList = new ArrayList<>();
        List<String> valueList = new ArrayList<>();
        List<Object> parameters = new ArrayList<>();

        for (Map.Entry<String, Object> kvp : map.entrySet()) {
            fieldList.add(kvp.getKey());
            valueList.add("?");
            parameters.add(kvp.getValue());
        }

        String sql = "insert into ".concat(tableName).concat(" (").concat(String.join(",", fieldList)).concat(") values (").concat(String.join(",", valueList)).concat(")");

        return this.ExceNoQuery(sql, parameters);
    }

    private void ExcelQueryToResultSet(String sql, List<Object> parameters) throws SQLException {
        this.CreateConnection();

        if (parameters == null) {
            this._Statement = this._Connection.createStatement();
            this._ResultSet = this._Statement.executeQuery(sql);
        } else {
            this._PrearedStatement = this._Connection.prepareStatement(sql);
            SetPreparedStatementParameters(this._PrearedStatement, parameters);
            this._ResultSet = this._PrearedStatement.executeQuery();
        }
    }

    public List<Map<String, Object>> ExceSelect(String sql, List<Object> parameters) {
        try {
            this.ExcelQueryToResultSet(sql, parameters);
            return this.ResultSetToList();
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
        catch (Exception ex){
            ex.printStackTrace();
            return null;
        }
        finally {
            this.CloseConnction();
        }
    }

    private List<Map<String, Object>> ResultSetToList() throws SQLException, Exception {
        if (this._ResultSet != null) {
            List<Map<String, Object>> list = new ArrayList<>();

            Map<String, Object> map = null;

            ResultSetMetaData metaData = this._ResultSet.getMetaData();
            int iColNum = metaData.getColumnCount();
            String columnName = "";
            Object value=null;
            while (this._ResultSet.next()) {
                map = new HashMap<>();

                for (int i = 1; i <= iColNum; i++) {
                    columnName = metaData.getColumnLabel(i);
                    value = _ResultSet.getObject(i);
                    if(value instanceof  NClob) value= Clob2String((NClob)value);
                    map.put(columnName, value);
                }

                list.add(map);
            }
            return list;
        }

        return null;
    }

    static String Clob2String(NClob nclob) throws Exception {
        String content = "";

        Reader is = nclob.getCharacterStream();
        BufferedReader buff = new BufferedReader(is);// 得到流
        String line = buff.readLine();
        StringBuffer sb = new StringBuffer();
        while (line != null) {// 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
            sb.append(line);
            line = buff.readLine();
        }
        content = sb.toString();

        return content;
    }

    List<Map<String, Object>> Select(String tableName, String selectFields, Map<String, Object> whereMap) {
        List<String> whereFields = new ArrayList<>();
        List<Object> parameters = new ArrayList<>();

        String whereSql = "";
        if (whereMap != null && !whereMap.isEmpty()) {
            for (Map.Entry<String, Object> kvp : whereMap.entrySet()) {
                whereFields.add(kvp.getKey().concat("=?"));
                parameters.add(kvp.getValue());
            }
            whereSql = " where ".concat(String.join(" and ", whereFields));
        }

        String sql = "select ".concat(selectFields).concat(" from ").concat(tableName).concat(whereSql);

        List<Map<String, Object>> list = this.ExceSelect(sql, parameters);
        return list == null ? new ArrayList<>() : list;
    }

    boolean Delete(String tableName, Map<String, Object> whereMap) {
        List<String> whereFields = new ArrayList<>();
        List<Object> parameters = new ArrayList<>();

        String whereSql = "";
        if (whereMap != null && !whereMap.isEmpty()) {
            for (Map.Entry<String, Object> kvp : whereMap.entrySet()) {
                whereFields.add(kvp.getKey().concat("=?"));
                parameters.add(kvp.getValue());
            }
            whereSql = " where ".concat(String.join(" and ", whereFields));
        }

        String sql = String.format("delete from %s %s", tableName, whereSql);

        return this.ExceNoQuery(sql, parameters) > 0;
    }

    public void InitData() {
        List<Map<String,Object>> products = this.Select(_TableName, "*", null);
        if(products.isEmpty()) {
            Map<String, Object> map = new HashMap<>();
            map.put("id",101);
            map.put("pname", "数码相机");
            map.put("brand", "奥林巴斯");
            map.put("price", 1330);
            map.put("stock", 3);
            if (this.Insert(_TableName, map) > 0) System.out.println("添加成功");

            map = new HashMap<>();
            map.put("id", 102);
            map.put("pname", "平板电脑");
            map.put("brand", "苹果");
            map.put("price", 1999);
            map.put("stock", 5);
            if (this.Insert(_TableName, map) > 0) System.out.println("添加成功");
        }
    }

    public void OutputProducts() {
        List<Map<String, Object>> products = this.Select(_TableName, "*", null);

        PrintProducts(products);
    }

    void  PrintProducts(List<Map<String,Object>> products){
        System.out.println("产品ID    产品名称   品牌    价格     库存");

        products.forEach(p -> {
            int id = (int) p.get("id");
            String pname = (String) p.get("pname");
            String brand = (String) p.get("brand");
            float price = (float) p.get("price");
            int stock = (int) p.get("stock");

            System.out.println(String.format("%s    %s   %s    %s     %s", id, pname, brand, price, stock));
        });
    }

    public void AddProduct(){
        Map<String,Object> whereMap = new HashMap<>();
        whereMap.put("id",109);

        Delete(_TableName, whereMap);

        Map<String, Object> map = new HashMap<>();
        map.put("id",109);
        map.put("pname", "3G手机");
        map.put("brand", "Sangsung");
        map.put("price", 3500);
        map.put("stock", 10);
        if (this.Insert(_TableName, map) > 0) System.out.println("添加成功");
    }

    public void DeleteProduct(int id){
        Map<String,Object> whereMap = new HashMap<>();
        whereMap.put("id", id);
        Map<String, Object> map = GetFirstOrDefault(this.Select(_TableName, "*", whereMap));
        if(map==null) {
            System.out.println(String.format("产品编号：%s对应的产品不存在", id));
        }
        else {
            boolean blSucceed = Delete(_TableName, whereMap);
            System.out.println(String.format("删除%s", blSucceed ? "成功" : "失败"));
        }
    }

    public Map<String, Object> GetFirstOrDefault(List<Map<String, Object>> list) {
        if (list != null && !list.isEmpty()) {
            return list.get(0);
        }
        return null;
    }

    public void  SelectProduct(int id){
        Map<String,Object> whereMap = new HashMap<>();
        whereMap.put("id", id);
        List<Map<String, Object>> list = this.Select(_TableName, "*", whereMap);
        if(list.isEmpty()) {
            System.out.println("无法找到此商品!");
        }
        else {
            PrintProducts(list);
        }
    }
}
